/**
 * 同步到同服务器的备份数据库
 * 将主数据库内容同步到同一服务器的备份数据库
 * 如果备份数据库不存在，会跳过创建步骤
 */

const mysql = require('mysql2/promise');

// 数据库配置
const SOURCE_DB = {
  host: '43.143.253.188',
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

// 备份数据库配置（使用不同的数据库名）
const BACKUP_DB = {
  host: '43.143.253.188',
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs_backup',  // 备份数据库名
  charset: 'utf8mb4'
};

// 关键表列表
const KEY_TABLES = [
  // 娱乐城相关表
  'casino_dice_rounds',
  'casino_chibi_rounds',
  'casino_dice_bets',
  'casino_chibi_bets',
  'casino_rock_paper_scissors',
  
  // 副将相关表
  'deputy_general_base',
  'deputy_general',
  'player_deputy',
  'deputy_general_skill_learned',
  'deputy_general_stone',
  'deputy_general_equipment',
  'player_deputy_warehouse',
  
  // 装备相关表
  'equipment_base',
  'character_equipment_inventory',
  'character_equipment',
  
  // 基础数据表
  'basic_items',
  'skill_base',
  'level_experience',
  'mount_base',
  'map_areas',
  
  // 角色相关表
  'users',
  'characters',
  'character_attributes',
  'character_inventory',
  'character_mounts',
  
  // 系统消息表
  'system_messages',
  'private_messages',
  'chat_messages',
  'announcements'
];

class SameServerBackupSync {
  constructor() {
    this.sourceConnection = null;
    this.backupConnection = null;
    this.syncLog = [];
    this.startTime = new Date();
  }

  async connect() {
    try {
      console.log('🔄 连接数据库...');
      
      // 连接源数据库
      console.log('  📡 连接源数据库...');
      this.sourceConnection = await mysql.createConnection(SOURCE_DB);
      await this.sourceConnection.execute("SET NAMES utf8mb4");
      await this.sourceConnection.execute("SET time_zone = '+08:00'");
      console.log('  ✅ 源数据库连接成功');
      
      // 尝试连接备份数据库
      console.log('  📡 连接备份数据库...');
      try {
        this.backupConnection = await mysql.createConnection(BACKUP_DB);
        await this.backupConnection.execute("SET NAMES utf8mb4");
        await this.backupConnection.execute("SET time_zone = '+08:00'");
        console.log('  ✅ 备份数据库连接成功');
      } catch (error) {
        if (error.code === 'ER_BAD_DB_ERROR' || error.code === 'ER_DBACCESS_DENIED_ERROR') {
          console.log('  ⚠️ 备份数据库不存在或无访问权限，将导出为SQL文件');
          console.log('  💡 建议：请管理员创建备份数据库或使用SQL文件进行备份');
          this.backupConnection = null;
        } else {
          throw error;
        }
      }
      
      console.log('✅ 数据库连接完成');
    } catch (error) {
      console.error('❌ 数据库连接失败:', error.message);
      throw error;
    }
  }

  async disconnect() {
    if (this.sourceConnection) await this.sourceConnection.end();
    if (this.backupConnection) await this.backupConnection.end();
    console.log('✅ 数据库连接已关闭');
  }

  async getTableList() {
    try {
      const [tables] = await this.sourceConnection.execute(`
        SELECT TABLE_NAME FROM information_schema.tables 
        WHERE table_schema = ? AND table_type = 'BASE TABLE'
        ORDER BY TABLE_NAME
      `, [SOURCE_DB.database]);
      
      return tables.map(t => t.TABLE_NAME);
    } catch (error) {
      console.error('❌ 获取表列表失败:', error.message);
      return [];
    }
  }

  async exportTableToSQL(tableName) {
    try {
      console.log(`📤 导出表 ${tableName} 到SQL文件...`);
      
      // 获取表结构
      const [createTableResult] = await this.sourceConnection.execute(`
        SHOW CREATE TABLE \`${tableName}\`
      `);
      const createTableSQL = createTableResult[0]['Create Table'];
      
      // 获取表数据
      const [rows] = await this.sourceConnection.execute(`SELECT * FROM \`${tableName}\``);
      
      // 生成SQL文件内容
      let sqlContent = `-- 表 ${tableName} 的备份\n`;
      sqlContent += `-- 导出时间: ${new Date().toISOString()}\n\n`;
      sqlContent += `DROP TABLE IF EXISTS \`${tableName}\`;\n`;
      sqlContent += `${createTableSQL};\n\n`;
      
      if (rows.length > 0) {
        const columns = Object.keys(rows[0]);
        const columnNames = columns.map(col => `\`${col}\``).join(', ');
        
        sqlContent += `-- 插入数据 (${rows.length} 行)\n`;
        
        for (const row of rows) {
          const values = columns.map(col => {
            const value = row[col];
            if (value === null) return 'NULL';
            if (typeof value === 'string') return `'${value.replace(/'/g, "''")}'`;
            if (value instanceof Date) {
              // 处理无效日期
              if (isNaN(value.getTime())) {
                return 'NULL';
              }
              return `'${value.toISOString().slice(0, 19).replace('T', ' ')}'`;
            }
            if (typeof value === 'object' && value !== null) {
              // 处理JSON对象
              return `'${JSON.stringify(value).replace(/'/g, "''")}'`;
            }
            return value;
          }).join(', ');
          
          sqlContent += `INSERT INTO \`${tableName}\` (${columnNames}) VALUES (${values});\n`;
        }
      }
      
      // 保存到文件
      const fs = require('fs');
      const path = require('path');
      const fileName = `backup_${tableName}_${new Date().toISOString().slice(0, 10)}.sql`;
      const filePath = path.join(__dirname, 'backup', fileName);
      
      // 确保备份目录存在
      const backupDir = path.dirname(filePath);
      if (!fs.existsSync(backupDir)) {
        fs.mkdirSync(backupDir, { recursive: true });
      }
      
      fs.writeFileSync(filePath, sqlContent, 'utf8');
      console.log(`✅ 表 ${tableName} 已导出到 ${filePath}`);
      
      return { success: true, filePath, rows: rows.length };
      
    } catch (error) {
      console.error(`❌ 导出表 ${tableName} 失败:`, error.message);
      return { success: false, error: error.message };
    }
  }

  async syncTable(tableName) {
    try {
      console.log(`\n🔄 同步表: ${tableName}`);
      
      // 检查源表是否存在
      const [sourceTables] = await this.sourceConnection.execute(`
        SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
      `, [SOURCE_DB.database, tableName]);
      
      if (sourceTables[0].count === 0) {
        console.log(`⚠️ 源数据库中不存在表 ${tableName}，跳过`);
        return { success: false, reason: '表不存在' };
      }

      // 获取源表数据
      const [rows] = await this.sourceConnection.execute(`SELECT * FROM \`${tableName}\``);
      console.log(`📊 表 ${tableName} 有 ${rows.length} 行数据`);

      if (rows.length === 0) {
        console.log(`ℹ️ 表 ${tableName} 无数据，跳过同步`);
        return { success: true, rows: 0 };
      }

      // 如果有备份数据库连接，进行数据库同步
      if (this.backupConnection) {
        return await this.syncTableToDatabase(tableName, rows);
      } else {
        // 否则导出到SQL文件
        return await this.exportTableToSQL(tableName);
      }

    } catch (error) {
      console.error(`❌ 同步表 ${tableName} 失败:`, error.message);
      return { success: false, error: error.message };
    }
  }

  async syncTableToDatabase(tableName, rows) {
    try {
      // 检查备份表是否存在，不存在则创建
      const [backupTables] = await this.backupConnection.execute(`
        SELECT COUNT(*) as count FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
      `, [BACKUP_DB.database, tableName]);

      if (backupTables[0].count === 0) {
        // 获取创建表的SQL
        const [createTableResult] = await this.sourceConnection.execute(`
          SHOW CREATE TABLE \`${tableName}\`
        `);
        const createTableSQL = createTableResult[0]['Create Table'];
        await this.backupConnection.execute(createTableSQL);
        console.log(`✅ 在备份数据库中创建表 ${tableName}`);
      }

      // 清空备份表
      await this.backupConnection.execute(`TRUNCATE TABLE \`${tableName}\``);
      console.log(`🗑️ 已清空备份表 ${tableName}`);

      // 批量插入数据
      if (rows.length > 0) {
        const columns = Object.keys(rows[0]);
        const placeholders = columns.map(() => '?').join(', ');
        const columnNames = columns.map(col => `\`${col}\``).join(', ');
        const insertSQL = `INSERT INTO \`${tableName}\` (${columnNames}) VALUES (${placeholders})`;

        // 分批插入，每批1000条
        const batchSize = 1000;
        for (let i = 0; i < rows.length; i += batchSize) {
          const batch = rows.slice(i, i + batchSize);
          for (const row of batch) {
            const values = columns.map(col => row[col]);
            await this.backupConnection.execute(insertSQL, values);
          }
          const progress = Math.round(((i + batchSize) / rows.length) * 100);
          console.log(`📈 表 ${tableName} 插入进度: ${Math.min(i + batchSize, rows.length)}/${rows.length} (${progress}%)`);
        }
      }

      console.log(`✅ 表 ${tableName} 同步完成`);
      return { success: true, rows: rows.length };

    } catch (error) {
      console.error(`❌ 同步表 ${tableName} 到数据库失败:`, error.message);
      return { success: false, error: error.message };
    }
  }

  async syncAllKeyTables() {
    const results = {
      total: KEY_TABLES.length,
      success: 0,
      failed: 0,
      details: []
    };

    console.log(`🔄 开始同步 ${KEY_TABLES.length} 个关键表...\n`);

    for (const tableName of KEY_TABLES) {
      const result = await this.syncTable(tableName);
      
      if (result.success) {
        results.success++;
        results.details.push({
          table: tableName,
          status: 'success',
          rows: result.rows || 0,
          filePath: result.filePath
        });
      } else {
        results.failed++;
        results.details.push({
          table: tableName,
          status: 'failed',
          error: result.error || result.reason
        });
      }
    }

    return results;
  }

  printSummary(results) {
    const endTime = new Date();
    const duration = Math.round((endTime - this.startTime) / 1000);

    console.log('\n📊 同步总结:');
    console.log(`  总表数: ${results.total}`);
    console.log(`  成功: ${results.success}`);
    console.log(`  失败: ${results.failed}`);
    console.log(`  成功率: ${Math.round((results.success / results.total) * 100)}%`);
    console.log(`  耗时: ${duration}秒`);

    if (this.backupConnection) {
      console.log(`  同步方式: 数据库同步`);
    } else {
      console.log(`  同步方式: SQL文件导出`);
      console.log(`  备份文件位置: ./backup/ 目录`);
    }

    if (results.failed > 0) {
      console.log('\n❌ 失败的表:');
      results.details
        .filter(detail => detail.status === 'failed')
        .forEach(detail => {
          console.log(`  - ${detail.table}: ${detail.error}`);
        });
    }
  }
}

// 主函数
async function main() {
  const sync = new SameServerBackupSync();
  
  try {
    // 连接数据库
    await sync.connect();
    
    // 同步关键表
    const results = await sync.syncAllKeyTables();
    sync.printSummary(results);
    
    console.log('\n🎉 数据库备份完成！');
    
    if (!sync.backupConnection) {
      console.log('\n💡 提示:');
      console.log('  由于备份数据库不存在，数据已导出为SQL文件');
      console.log('  您可以使用以下方式恢复数据:');
      console.log('  1. 创建备份数据库: CREATE DATABASE nextjs_backup;');
      console.log('  2. 导入SQL文件: mysql -u username -p nextjs_backup < backup_file.sql');
    }
    
  } catch (error) {
    console.error('❌ 备份失败:', error);
    process.exit(1);
  } finally {
    await sync.disconnect();
  }
}

// 处理程序退出
process.on('SIGINT', async () => {
  console.log('\n🛑 备份被中断');
  process.exit(0);
});

// 运行主函数
if (require.main === module) {
  main();
}

module.exports = SameServerBackupSync;
